﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Controller
{
    /// <summary>
    /// Summary description for MarketingActivity Controller
    /// </summary>
    public class NPSMarketingActivityController : ControllerBase
    {
        private void CreateNPSMarketingActivityNewsFeed(DBLib dbl, Entities.NPSMarketingActivity ciMarketingActivity)
        {

            NewsFeedData nfd = new NewsFeedData();
            try
            {
                NPSCustomerContactController ncc = new NPSCustomerContactController();
               nfd.iCustomerId = ncc.getNPSCustomerContact(Convert.ToInt32(ICPUser.ID), false).iCompanyID;
            }
            catch { nfd.iCustomerId = 1; }
            nfd.iSourceId = (int)NewsFeedSourceType.ICP_NPS;
            nfd.sxUserId_Poster = ICPUser.ID;
            nfd.sxTitle = "Marketing Activity has been updated!";
            nfd.sxContent = "Marketing Activity : " + ciMarketingActivity.sxTitle;
            CreateNewsFeed(dbl, nfd);
        }

        public int InsertNPSMarketingActivityData(Entities.NPSMarketingActivity ciMarketingActivity)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;
            
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.sxTitle;
 
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_LOCATION";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.sxLocation;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_NAME";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.iNPSMarketingName;
 
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_TYPE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.iNPSMarketingType;
            
            
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_QUOTA";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.iQuota;
            
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_START_DATE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.dtmStartDate;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_END_DATE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.dtmEndDate;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_I_NPS_MARKETING_ACTIVITY";
            
            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            CreateNPSMarketingActivityNewsFeed(dbl, ciMarketingActivity);
            return Convert.ToInt32(lsParams[0].Value);  
        }

        public void UpdateNPSMarketingActivityData(Entities.NPSMarketingActivity ciMarketingActivity)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_ACTIVITY_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.iID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.sxTitle;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_LOCATION";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.sxLocation;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_NAME";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.iNPSMarketingName;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_TYPE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.iNPSMarketingType;
            
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_QUOTA";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.iQuota;
            
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_START_DATE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.dtmStartDate;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_END_DATE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.dtmEndDate;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.sxUpdater;

            String sxQuery = "PR_U_NPS_MARKETING_ACTIVITY";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public void DeleteNPSMarketingActivityData(Entities.NPSMarketingActivity ciMarketingActivity)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_ACTIVITY_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingActivity.iID;

            String sxQuery = "PR_D_NPS_MARKETING_ACTIVITY";
            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public List<Entities.NPSMarketingActivity> NPSMarketingActivityList
        {
            get
            {
                return getNPSMarketingActivityList(false);
            }
        }

        public List<Entities.NPSMarketingActivity> NPSDetailedMarketingActivityList
        {
            get
            {
                return getNPSMarketingActivityList(true);
            }
        }

        public List<Entities.NPSMarketingActivity> getNPSMarketingActivityList(string name, string type, DateTime start, DateTime end)
        { 
        DBLib dbl = new DBLib();
            String sxQuery = @"
                select 
                    NPS_MARKETING_ACTIVITY_ID,
	                TITLE,
	                DETAIL,
	                KUOTA,
	                LOCATION,
	                P_NPS_MARKETING_NAME_ID,
	                P_NPS_MARKETING_TYPE_ID,
	                START_DATE,
	                END_DATE,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
                    VALID_FROM,
                    VALID_TO
                from 
                    NPS_MARKETING_ACTIVITY
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
                   AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE
                   and activity_date >='"+start.ToString("dd-MMM-yyyy")+"' and activity_date <='"+end.ToString("dd-MMM-yyyy")+@"'
                ";

            if (name != "0" && !string.IsNullOrEmpty(name))
            { sxQuery += " and P_NPS_MARKETING_NAME_ID=" + name; }
            if (type != "0" && !string.IsNullOrEmpty(type))
            { sxQuery += " and P_NPS_MARKETING_TYPE_ID=" + type; }
            DataTable dt = dbl.GetDataTable(sxQuery);

            List<Entities.NPSMarketingActivity> MarketingActivityList = new List<Entities.NPSMarketingActivity>();
            Controller.NPSMarketingActivityAttendantController MAAController = new NPSMarketingActivityAttendantController();
            NPSMarketingNameController ANController = new NPSMarketingNameController();
            NPSMarketingTypeController ATController = new NPSMarketingTypeController();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSMarketingActivity ciMarketingActivity = new Entities.NPSMarketingActivity();
                ciMarketingActivity.iID = Convert.ToInt16(row["NPS_Marketing_Activity_ID"].ToString());
                ciMarketingActivity.iNPSMarketingName = Convert.ToInt16(row["P_NPS_MARKETING_NAME_ID"].ToString());
                ciMarketingActivity.iNPSMarketingType = Convert.ToInt16(row["P_NPS_MARKETING_TYPE_ID"].ToString());
                ciMarketingActivity.sxTitle = row["TITLE"].ToString();
                ciMarketingActivity.sxDetail = row["DETAIL"].ToString();
                ciMarketingActivity.sxLocation = row["LOCATION"].ToString();
                ciMarketingActivity.dtmStartDate = DateTime.Parse(dt.Rows[0]["START_DATE"].ToString());
                ciMarketingActivity.dtmEndDate = DateTime.Parse(dt.Rows[0]["END_DATE"].ToString());
                ciMarketingActivity.sxCreator = row["CREATE_BY"].ToString();
                ciMarketingActivity.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                ciMarketingActivity.sxUpdater = row["UPDATE_BY"].ToString();
                ciMarketingActivity.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                ciMarketingActivity.NPSMarketingActivityAttendantList = MAAController.getNPSMarketingActivityAttendantList(ciMarketingActivity);
                ciMarketingActivity.oNPSMarketingName = ANController.getNPSMarketingName(ciMarketingActivity.iNPSMarketingName);
                ciMarketingActivity.oNPSMarketingType = ATController.getNPSMarketingType(ciMarketingActivity.iNPSMarketingType);
                MarketingActivityList.Add(ciMarketingActivity);
            }
            return MarketingActivityList;
        
        }

        protected List<Entities.NPSMarketingActivity> getNPSMarketingActivityList(bool isDetailed)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"
               select 
                    NPS_MARKETING_ACTIVITY_ID,
	                TITLE,
	                DETAIL,
	                KUOTA,
	                LOCATION,
	                P_NPS_MARKETING_NAME_ID,
	                P_NPS_MARKETING_TYPE_ID,
	                START_DATE,
	                END_DATE,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
                    VALID_FROM,
                    VALID_TO
                from 
                    NPS_MARKETING_ACTIVITY
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
                    AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE
                ";

            DataTable dt = dbl.GetDataTable(sxQuery);

            List<Entities.NPSMarketingActivity> MarketingActivityList = new List<Entities.NPSMarketingActivity>();
            Controller.NPSMarketingActivityAttendantController MAAController = new NPSMarketingActivityAttendantController();
            NPSMarketingNameController ANController = new NPSMarketingNameController();
            NPSMarketingTypeController ATController = new NPSMarketingTypeController();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSMarketingActivity ciMarketingActivity = new Entities.NPSMarketingActivity();
                ciMarketingActivity.iID = Convert.ToInt16(row["NPS_Marketing_Activity_ID"].ToString());
                ciMarketingActivity.iNPSMarketingName = Convert.ToInt16(row["P_NPS_MARKETING_NAME_ID"].ToString());
                ciMarketingActivity.iNPSMarketingType = Convert.ToInt16(row["P_NPS_MARKETING_TYPE_ID"].ToString());
                ciMarketingActivity.sxTitle = row["TITLE"].ToString();
                ciMarketingActivity.sxDetail = row["DETAIL"].ToString();
                ciMarketingActivity.sxLocation = row["LOCATION"].ToString();
                ciMarketingActivity.dtmStartDate = DateTime.Parse(dt.Rows[0]["START_DATE"].ToString());
                //ciMarketingActivity.dtmEndDate = dt.Rows[0]["END_DATE"] != null ? DateTime.Parse(dt.Rows[0]["END_DATE"].ToString()) : DateTime.Now;
                ciMarketingActivity.sxCreator = row["CREATE_BY"].ToString();
                ciMarketingActivity.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                ciMarketingActivity.sxUpdater = row["UPDATE_BY"].ToString();
                ciMarketingActivity.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                if (isDetailed)
                {
                    ciMarketingActivity.NPSMarketingActivityAttendantList = MAAController.getNPSMarketingActivityAttendantList(ciMarketingActivity);
                    ciMarketingActivity.oNPSMarketingName = ANController.getNPSMarketingName(ciMarketingActivity.iNPSMarketingName);
                    ciMarketingActivity.oNPSMarketingType = ATController.getNPSMarketingType(ciMarketingActivity.iNPSMarketingType);
                }
                MarketingActivityList.Add(ciMarketingActivity);
            }
            return MarketingActivityList;
        }

        public Entities.NPSMarketingActivity getNPSMarketingActivity(int MarketingActivityID, bool isDetailed)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select 
                    NPS_MARKETING_ACTIVITY_ID,
	                TITLE,
	                DETAIL,
	                KUOTA,
	                LOCATION,
	                P_NPS_MARKETING_NAME_ID,
	                P_NPS_MARKETING_TYPE_ID,
	                START_DATE,
	                END_DATE,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
                    VALID_FROM,
                    VALID_TO
                from 
                    NPS_MARKETING_ACTIVITY
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    NPS_MARKETING_ACTIVITY_ID = :prmID
                ";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = MarketingActivityID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            Entities.NPSMarketingActivity ciMarketingActivity = new Entities.NPSMarketingActivity();
            try
            {
                ciMarketingActivity.iID = Convert.ToInt16(dt.Rows[0]["NPS_MarketingActivity_ID"].ToString());
                ciMarketingActivity.iNPSMarketingName = Convert.ToInt16(dt.Rows[0]["P_NPS_MARKETING_NAME_ID"].ToString());
                ciMarketingActivity.iNPSMarketingType = Convert.ToInt16(dt.Rows[0]["P_NPS_MARKETING_TYPE_ID"].ToString());
                ciMarketingActivity.sxTitle = dt.Rows[0]["TITLE"].ToString();
                ciMarketingActivity.sxDetail = dt.Rows[0]["DETAIL"].ToString();
                ciMarketingActivity.sxLocation = (dt.Rows[0]["LOCATION"].ToString());
                ciMarketingActivity.dtmStartDate = DateTime.Parse(dt.Rows[0]["START_DATE"].ToString());
                ciMarketingActivity.dtmEndDate = DateTime.Parse(dt.Rows[0]["END_DATE"].ToString());
                ciMarketingActivity.sxCreator = dt.Rows[0]["CREATE_BY"].ToString();
                ciMarketingActivity.dtmCreated = DateTime.Parse(dt.Rows[0]["CREATE_DATE"].ToString());
                ciMarketingActivity.sxUpdater = dt.Rows[0]["UPDATE_BY"].ToString();
                ciMarketingActivity.dtmUpdated = DateTime.Parse(dt.Rows[0]["UPDATE_DATE"].ToString());

                if (isDetailed)
                {
                    Controller.NPSMarketingActivityAttendantController MAAController = new NPSMarketingActivityAttendantController();
                    ciMarketingActivity.NPSMarketingActivityAttendantList = MAAController.getNPSMarketingActivityAttendantList(ciMarketingActivity);
                    NPSMarketingNameController ANController = new NPSMarketingNameController();
                    ciMarketingActivity.oNPSMarketingName = ANController.getNPSMarketingName(ciMarketingActivity.iNPSMarketingName);
                    NPSMarketingTypeController ATController = new NPSMarketingTypeController();
                    ciMarketingActivity.oNPSMarketingType = ATController.getNPSMarketingType(ciMarketingActivity.iNPSMarketingType);
                }
            }
            catch (Exception p) { errLog = p.ToString(); }
            return ciMarketingActivity;
        }

    }
}